﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Photos
{
    internal static class PhotoDAL
    {
        internal static PhotoEntity GetPhotoByReader(IDataReader dr)
        {
            PhotoEntity photo = new PhotoEntity();

            photo.PhotoID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            photo.ObjectID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            photo.ObjectType = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            photo.ImgUrl = dr.IsDBNull(3) ? "Null.jpg" : dr.GetString(3);
            photo.Created = Util.ConverDateTime(dr.GetValue(4));
            photo.Name = dr.IsDBNull(5) ? null : dr.GetString(5);
            photo.JumpUrl = dr.IsDBNull(6) ? "#" : dr.GetString(6);
            photo.Target = dr.IsDBNull(7) ? 1 : dr.GetInt32(7);

            return photo;
        }

        internal static int GetPhotoCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectPhoto = @"SELECT COUNT([PhotoID]) FROM [Photo]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectPhoto, ref parameterlst);
            DbCommand SelectPhotoCommand = db.GetSqlStringCommand(SelectPhoto);
            DbHelper.PackDbCommandByDbParameters(SelectPhotoCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectPhotoCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<PhotoEntity> GetPhotoList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<PhotoEntity> photolst = new List<PhotoEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectPhoto = @"
                    SELECT [PhotoID],[ObjectID],[ObjectType],[ImgUrl],[Created],[Name],[JumpUrl],[Target]
                    FROM (
                        SELECT [PhotoID],[ObjectID],[ObjectType],[ImgUrl],[Created],[Name],[JumpUrl],[Target],
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectPhoto += " " + sortField + " " + sortMode + ",";
            SelectPhoto += @" [Photo].[PhotoID] Desc
                    ) AS rowNum 
                        FROM [Photo] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectPhoto, ref parameterlst);
            SelectPhoto += @"
                    ) AS [Photo] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectPhotoCommand = db.GetSqlStringCommand(SelectPhoto);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectPhotoCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectPhotoCommand))
            {
                while (dr.Read())
                {
                    photolst.Add(GetPhotoByReader(dr));
                }
            }

            return photolst;
        }

        internal static List<PhotoEntity> GetPhotoList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<PhotoEntity> photolst = new List<PhotoEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectPhoto = @"
                    SELECT [PhotoID],[ObjectID],[ObjectType],[ImgUrl],[Created],[Name],[JumpUrl],[Target]
                    FROM [Photo] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectPhoto, ref parameterlst);
            SelectPhoto += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectPhoto += " " + sortField + " " + sortMode + ",";
            SelectPhoto += " [Photo].[PhotoID] Asc ";

            DbCommand SelectPhotoCommand = db.GetSqlStringCommand(SelectPhoto);
            DbHelper.PackDbCommandByDbParameters(SelectPhotoCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectPhotoCommand))
            {
                while (dr.Read())
                {
                    photolst.Add(GetPhotoByReader(dr));
                }
            }

            return photolst;
        }

        internal static PhotoEntity GetPhoto(int photoID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            PhotoEntity photo = null;

            string SelectPhoto = @"
                    SELECT [PhotoID],[ObjectID],[ObjectType],[ImgUrl],[Created],[Name],[JumpUrl],[Target]
                    FROM [Photo] 
                    WHERE [Photo].[PhotoID]=@PhotoID
                ";

            DbCommand SelectPhotoCommand = db.GetSqlStringCommand(SelectPhoto);
            db.AddInParameter(SelectPhotoCommand, "@PhotoID", DbType.Int32, photoID);

            using (IDataReader dr = db.ExecuteReader(SelectPhotoCommand))
            {
                if (dr.Read())
                {
                    photo = GetPhotoByReader(dr);
                }
            }

            return photo;
        }

        internal static int InsertPhoto(PhotoEntity photo)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertPhoto = @"INSERT INTO [Photo] 
				([ObjectID],[ObjectType],[ImgUrl],[Created],[Name],[JumpUrl],[Target])
				 VALUES (@ObjectID,@ObjectType,@ImgUrl,@Created,@Name,@JumpUrl,Target)";

            DbCommand InsertPhotoCommand = db.GetSqlStringCommand(InsertPhoto);
            db.AddInParameter(InsertPhotoCommand, "@ObjectID", DbType.Int32, photo.ObjectID);
            db.AddInParameter(InsertPhotoCommand, "@ObjectType", DbType.Int32, photo.ObjectType);
            db.AddInParameter(InsertPhotoCommand, "@ImgUrl", DbType.String, photo.ImgUrl);
            db.AddInParameter(InsertPhotoCommand, "@Created", DbType.DateTime, photo.Created);
            db.AddInParameter(InsertPhotoCommand, "@Name", DbType.String, photo.Name);
            db.AddInParameter(InsertPhotoCommand, "@JumpUrl", DbType.String, photo.JumpUrl);
            db.AddInParameter(InsertPhotoCommand, "@Target", DbType.Int32, photo.Target);

            result = db.ExecuteNonQuery(InsertPhotoCommand);

            return result;
        }

        internal static int UpdatePhoto(PhotoEntity photo)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdatePhoto = @"UPDATE [Photo] 
				 SET [ObjectID]=@ObjectID,[ObjectType]=@ObjectType,[ImgUrl]=@ImgUrl,[Created]=@Created,
                    [Name]=@Name,JumpUrl=@JumpUrl,Target=@Target
				 WHERE [PhotoID] = @PhotoID";

            DbCommand UpdatePhotoCommand = db.GetSqlStringCommand(UpdatePhoto);
            db.AddInParameter(UpdatePhotoCommand, "@PhotoID", DbType.Int32, photo.PhotoID);
            db.AddInParameter(UpdatePhotoCommand, "@ObjectID", DbType.Int32, photo.ObjectID);
            db.AddInParameter(UpdatePhotoCommand, "@ObjectType", DbType.Int32, photo.ObjectType);
            db.AddInParameter(UpdatePhotoCommand, "@ImgUrl", DbType.String, photo.ImgUrl);
            db.AddInParameter(UpdatePhotoCommand, "@Created", DbType.DateTime, photo.Created);
            db.AddInParameter(UpdatePhotoCommand, "@Name", DbType.String, photo.Name);
            db.AddInParameter(UpdatePhotoCommand, "@JumpUrl", DbType.String, photo.JumpUrl);
            db.AddInParameter(UpdatePhotoCommand, "@Target", DbType.Int32, photo.Target);

            result = db.ExecuteNonQuery(UpdatePhotoCommand);

            return result;
        }

        internal static int DeletePhoto(int photoID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeletePhoto = @"
                DELETE FROM [Photo] WHERE PhotoID=@PhotoID";

            DbCommand DeletePhotoCommand = db.GetSqlStringCommand(DeletePhoto);
            db.AddInParameter(DeletePhotoCommand, "@PhotoID", DbType.Int32, photoID);
            result = db.ExecuteNonQuery(DeletePhotoCommand);

            return result;
        }

        internal static int SetPhotoEnable(int photoID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetPhoto = @"UPDATE [Photo]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [PhotoID] = @PhotoID";

            DbCommand SetPhotoCommand = db.GetSqlStringCommand(SetPhoto);
            db.AddInParameter(SetPhotoCommand, "@PhotoID", DbType.Int32, photoID);
            result = db.ExecuteNonQuery(SetPhotoCommand);

            return result;
        }

        internal static int HitPhoto(int photoID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetPhoto = @"UPDATE [Photo]
                    SET [Hits] = [Hits]+1
                    WHERE [PhotoID] = @PhotoID";

            DbCommand SetPhotoCommand = db.GetSqlStringCommand(SetPhoto);
            db.AddInParameter(SetPhotoCommand, "@PhotoID", DbType.Int32, photoID);
            result = db.ExecuteNonQuery(SetPhotoCommand);

            return result;
        }
    }
}
